﻿using Dapper;

using System.Collections.Generic;
using System.Data.SqlClient;

namespace Geeno.API.Services
{
    using Model;

    using System;
    using System.Data;
    using System.Linq;

    public sealed class OrderService : CommonService, IOrderService
    {
        public IEnumerable<Item> GetItemsM()
        {
            var tmpM = new Item();

            var rlt = new List<Item>();
            var sql = @"
            SELECT TOP 1 ID
            FROM bas.Item
            WHERE ID = 1
            ;

            SELECT Amount
	             , ID,CreateDate=CONCAT(CONVERT(DATETIME2(0), CreateDate,120),'')
            FROM sale.[Order] where id=9
            ;
            ";

            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                conn.Open();

                var tmpRlt = conn.QueryMultiple(sql);
                var tmpItemID = tmpRlt.Read<int>().FirstOrDefault();
                var tmpOrders = tmpRlt.Read<Order>().AsList();
                var n = tmpOrders.Select(x => new { key = x.Amount }).ToList();

            }
            return rlt;
        }
        public IEnumerable<Order> GetOrders()
        {
            var rlt = new List<Order>();
            var sql = @"
            select *
            from 
            sale.[Order]";
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                //dynamic读取处理
                var tmpDy = conn.Query(sql, transaction: tran).AsList();

                rlt = conn.Query<Order>(sql, transaction: tran).AsList();
                tran.Commit();
            }
            return rlt;
        }

        public IEnumerable<Item> GetItems()
        {
            //sql 注入异常
            var name = "'or 1=1--";
            name = "测试商品";
            var rlt = new List<Item>();
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                //切记一定要用参数的方式进行处理
                conn.Open();
                var sql = @$"
                SELECT *
                FROM bas.Item where itemname=@name;";
                rlt = conn.Query<Item>(sql, new { name = name }).AsList();
            }
            return rlt;
        }
        /// <summary>
        /// 行内直接使用tvp进行批量数据写入处理。
        /// 目前有点遗忘adonet的操作处理，所以这里暂时还是用dapper结合存储过程的方式进行操作。
        /// </summary>
        private void insertWithTvpInline()
        {
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                //切记一定要用参数的方式进行处理
                conn.Open();
                var sqlBulkInsert = @"
insert into bas.item
select next value for dbo.seq_order,ItemName,Price
from @tvpItem
";
                using (var command = new SqlCommand(sqlBulkInsert, conn))
                {
                    var list = new List<ItemTvp>()
                {
                    new ItemTvp{ ItemName="测试tvp1",Price=100},
                    new ItemTvp{ ItemName="测试tvp2",Price=100},
                    new ItemTvp{ ItemName="测试tvp3",Price=100},
                };
                    var tvp = ext.ListToTable(list);
                    var param = command.Parameters.AddWithValue("@tvpItem", tvp);
                    param.SqlDbType = SqlDbType.Structured;
                    param.TypeName = "dbo.tvpItem";

                    var x = command.ExecuteNonQuery();
                }
            }
            }

        private string sqlInsertError()
        {
            //这种不带参数的查询就异常处理
            var name = "'or 1=1--";
            var sql = @$"
                SELECT *
                FROM bas.Item where itemname='{name}';";
            return sql;
        }

        public Order GetSingleOrder()
        {
            var rlt = new Order();
            var sql = @"
            select ID
            from 
            sale.[Order] where id=133010;";
            var sql2 = @"
            select ID
            from 
            sale.[Order] where id=1010;";
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                conn.Open();
                var tran = conn.BeginTransaction();

                var m = conn.QueryMultiple(sql + sql2, transaction: tran);

                var m1 = m.ReadFirstOrDefault<int>();


                var tmpRlt = conn.QuerySingleOrDefault(sql, transaction: tran);
                if (tmpRlt != null)
                {
                    var tmpID = tmpRlt.ID;
                    var tmpCreateDate = tmpRlt.CreateDate;
                }

                tran.Commit();
            }
            return rlt;
        }

        /// <summary>
        /// 如果查询没有结果返回，那么默认返回：0
        /// </summary>
        /// <returns></returns>
        public int GetExecuteScalar()
        {
            var rlt = 0;
            var sql = @"
            select *
            from 
            sale.[Order] where id=10310";
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                var tmpRlt = conn.ExecuteScalar<int>(sql, transaction: tran);

                tran.Commit();
            }
            return rlt;
        }

        /// <summary>
        /// 如果执行没有影响行数的话，会返回-1
        /// </summary>
        /// <returns></returns>
        public int DelExecute()
        {
            var rlt = 0;
            var sql = @"
            if not exists (
	            SELECT ID
	            FROM sale.[Order]
	            WHERE ID = 102209)

            DELETE FROM sale.[Order]
            WHERE ID = 1009;";
            using (var conn = new SqlConnection(CONNECTIONSTRING))
            {
                conn.Open();
                var tran = conn.BeginTransaction();
                var tmpRlt = conn.Execute(sql, transaction: tran);
                tran.Commit();
            }
            return rlt;
        }

        public int TestDate()
        {
            using (var conn = DataBaseConfig.GetConnection())
            {
                //通过将参数转为table的方式查询，解决参数超过2100的错误
                var pms = string.Join(",", Enumerable.Range(1, 2500));
                var rlt = conn.Query<UserInfo>($"select ID,Name,BirthDay from dbo.userinfo where ID in " +
                    $"(select splitID from dbo.GetSplitID('{pms}',','))");

                var mm = rlt.OrderByDescending(x => x.BirthDay);
            }
            return 0;
        }
    }
    public interface IOrderService
    {
        IEnumerable<Order> GetOrders();
        IEnumerable<Item> GetItemsM();
        IEnumerable<Item> GetItems();
        Order GetSingleOrder();

        int GetExecuteScalar();
        int DelExecute();
        int TestDate();

    }
}
